import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime
import openpyxl

# 运费、毛利、成本的中间统计表
engine = create_engine('mysql+pymysql://jsbi:jsbi-1701@47.114.55.19:9011/biv1?charset=utf8')
con = engine.connect()

# 月份->店铺
# 【主推次推动销逻辑的添加】
# sqlCmd = " select * from 财务_拼多多周度订单明细 where 交易月份='5月' and 店铺='拼多多-第一森林旗舰店' limit 20000 "
# sqlCmd = " select 店铺,平台商品id,新金额 from 财务_拼多多周度订单明细_debug"
sqlPddOrderDetail = 'select c.店铺,c.分类,c.新金额,c.运费,c.成本,c.交易月份 as 月份,c.月主推次推动销 as 产品定位  from (SELECT distinct a.*,b.分类 FROM 财务_拼多多周度订单明细 a left join 平台_拼多多_商品ID_分类_字典表 b ON a.平台商品id = b.商品ID) c'
# sqlPddOrderDetail = 'select c.店铺,c.分类,c.新金额,c.运费,c.成本,c.交易月份 as 月份,c.月主推次推动销 as 产品定位  from (SELECT distinct a.*,b.分类 FROM 财务_拼多多周度订单明细_debug a left join 平台_拼多多_商品ID_分类_字典表 b ON a.平台商品id = b.商品ID) c'
df = pd.read_sql(sql=sqlPddOrderDetail, con=engine)
df.head()
# dfGroup = df.groupby(['店铺', '分类'], as_index=False).agg({'新金额汇总': {'新金额': "sum"}, '运费': "sum", '成本': "sum"})
dfGroupBug = df.groupby(['月份', '店铺', '分类'], as_index=False).agg({'新金额': "sum", '运费': "sum", '成本': "sum"})
# dfGroup.to_excel('D:/dfGroup_after_agg.xlsx', sheet_name="中间表_财务_拼多多周度订单明细")
# 消除一个bug，新金额作为分母了，因此分母是不能为0的
dfGroup = dfGroupBug[dfGroupBug['新金额'] != 0]
# 列和列计算
dfGroup['毛利'] = dfGroup['新金额'] - dfGroup['运费'] - dfGroup['成本']
dfGroup['运费占比'] = dfGroup['运费'] / dfGroup['新金额']
dfGroup['成本占比'] = dfGroup['成本'] / dfGroup['新金额']
dfGroup['毛利率'] = 1 - dfGroup['成本'] / dfGroup['新金额'] - dfGroup['运费'] / dfGroup['新金额']
# dfGroup.to_excel('D:/dfGroup.xlsx', sheet_name="中间表_财务_拼多多周度订单明细")

dfGroupShop = df.groupby(['月份', '店铺'], as_index=False).agg({'新金额': "sum", '运费': "sum", '成本': "sum"})
dfGroupShop.rename(columns={'新金额': '新金额店铺汇总', '运费': '运费店铺汇总', '成本': '成本店铺汇总'}, inplace=True)
dfGroupShop['运费平均线'] = dfGroupShop['运费店铺汇总'] / dfGroupShop['新金额店铺汇总']
dfGroupShop['成本平均线'] = dfGroupShop['成本店铺汇总'] / dfGroupShop['新金额店铺汇总']
dfGroupShop['毛利率平均线'] = (dfGroupShop['新金额店铺汇总'] - dfGroupShop['运费店铺汇总'] - dfGroupShop['成本店铺汇总']) / dfGroupShop[
    '新金额店铺汇总']
# dfGroupShop.to_excel('D:/dfGroupShop.xlsx', sheet_name="中间表_财务_拼多多周度订单明细")

dfAll = pd.merge(dfGroup, dfGroupShop, on=['月份', '店铺'], how='left')

# 分开写，没有这种写法
# dfNew1 = dfGroup['新金额'].sum()
# dfNew2 = dfGroup['运费'].sum()
# dfNew3 = dfGroup['成本'].sum()

# dfAll2 = dfAll.astype(object).where(pd.notnull(dfAll), None)
# 解决可能出现的超时问题bugfix 2020.08.18
con.connection.connection.ping(reconnect=True)
dfAll.to_sql(name='中间表_财务_拼多多周度订单明细', con=con, if_exists='append', index=False)
# dfAll.to_excel('D:/dfAll.xlsx', sheet_name="中间表_财务_拼多多周度订单明细")

debug = ''
